package org.csu.springjpetstore.persistence.impl;

import org.csu.springjpetstore.domain.Item;
import org.csu.springjpetstore.domain.Product;
import org.csu.springjpetstore.persistence.ItemDAO;
import org.csu.springjpetstore.persistence.util.DBUtil;
import org.csu.springjpetstore.persistence.util.JDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

public class ItemDAOImpl implements ItemDAO {
    private final JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    private static final String UPDATE_INVENTORY_QUANTITY = "UPDATE INVENTORY SET QTY = QTY - ?  WHERE ITEMID =?";
    private static final String GET_INVENTORY_QUANTITY = "SELECT QTY AS value FROM INVENTORY WHERE ITEMID = ?";
    private static final String GET_ITEM = "SELECT I.ITEMID," +
            "LISTPRICE," +
            "UNITCOST," +
            "SUPPLIER AS supplierId," +
            "I.PRODUCTID AS \"product.productId\"," +
            "NAME AS \"product.name\"," +
            "DESCN AS \"product.description\"," +
            "CATEGORY AS \"product.categoryId\"," +
            "STATUS,ATTR1 AS attribute1," +
            "ATTR2 AS attribute2," +
            "ATTR3 AS attribute3," +
            "ATTR4 AS attribute4," +
            "ATTR5 AS attribute5," +
            "QTY AS quantity " +
            "from ITEM I, " +
            "INVENTORY V, " +
            "PRODUCT P " +
            "where P.PRODUCTID = I.PRODUCTID " +
            "and I.ITEMID = V.ITEMID " +
            "and I.ITEMID = ?";
    private static final String GET_ITEM_LIST_BY_PRODUCT = "SELECT I.ITEMID," +
            "LISTPRICE," +
            "UNITCOST," +
            "SUPPLIER AS supplierId," +
            "I.PRODUCTID AS \"product.productId\"," +
            "NAME AS \"product.name\"," +
            "DESCN AS \"product.description\"," +
            "CATEGORY AS \"product.categoryId\"," +
            "STATUS,ATTR1 AS attribute1," +
            "ATTR2 AS attribute2," +
            "ATTR3 AS attribute3, " +
            "ATTR4 AS attribute4, " +
            "ATTR5 AS attribute5 " +
            "FROM ITEM I, PRODUCT P " +
            "WHERE P.PRODUCTID = I.PRODUCTID AND I.PRODUCTID = ?";

    /**
     * description 更新存货item剩余量
     *
     * @param param: <键(名字),值> itemId:商品id itemQuantity:减少的数量
     * @author huangzhangyan
     * @date 2021/10/31 18:36
     */
    @Override
    public void updateInventoryQuantity(Map<String, Object> param) {
        String itemId = (String) param.get("itemId");
        Integer itemQuantity = (Integer) param.get("itemQuantity");
        template.update(UPDATE_INVENTORY_QUANTITY, itemQuantity, itemId);
    }

    /**
     * description 获得存货剩余量
     *
     * @param itemId:
     * @return int
     * @author huangzhangyan
     * @date 2021/10/31 18:46
     */
    @Override
    public int getInventoryQuantity(String itemId) {
        Integer i;
        try {
            i = template.queryForObject(GET_INVENTORY_QUANTITY, Integer.TYPE, itemId);
        } catch (DataAccessException e) {
            i = 0;
        }
        return i == null ? 0 : i;
    }

    /**
     * description 通过productId获取ItemList
     *
     * @param productId:
     * @return List<Item>
     * @author huangzhangyan
     * @date 2021/10/31 23:15
     */
    @Override
    public List<Item> getItemListByProduct(String productId) {
        List<Item> list;
        try {
            list = template.query(GET_ITEM_LIST_BY_PRODUCT, new BeanPropertyRowMapper<>(Item.class), productId);
        } catch (DataAccessException e) {
            list = null;
        }
        return list;
    }


    /**
     * description 通过itemId获取Item 多表联合查询
     *
     * @param itemId:
     * @return Item
     * @author huangzhangyan
     * @date 2021/10/31 23:20
     */
    @Override
    public Item getItem(String itemId) {
        Item item = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(GET_ITEM);
            pStatement.setString(1, itemId);
            ResultSet resultSet = pStatement.executeQuery();
            if (resultSet.next()) {
                item = new Item();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));
                Product product = new Product();
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setDescription(resultSet.getString(7));
                product.setCategoryId(resultSet.getString(8));
                item.setProduct(product);
                item.setStatus(resultSet.getString(9));
                item.setAttribute1(resultSet.getString(10));
                item.setAttribute2(resultSet.getString(11));
                item.setAttribute3(resultSet.getString(12));
                item.setAttribute4(resultSet.getString(13));
                item.setAttribute5(resultSet.getString(14));
                item.setQuantity(resultSet.getInt(15));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return item;
    }
}



